Imports System.Data.SqlClient
Imports System.Data
Imports CAPA_Entidad
Imports CAPA_Utilitarios
Public Class CodigoBarraDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    Function existenciaArticuloBarra(ByVal codigoBarra As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT ART_Codigo,BAR_Codigo,UNM_Abreviatura,APC_Cantidad " & _
                                  "FROM VW_ConsultaBarra " & _
                                  "WHERE BAR_Codigo = '" & codigoBarra & "'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function verificarExistenciaXCODBARXUBI(ByVal codBarra As String, ByVal ubiCodigo As String, ByVal codOrden As String) As DataSet
        Dim query As String = ""
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            If codOrden <> "" Then
                query = "SELECT a.ART_Codigo, a.ART_Descripcion, dpad.DCA_Codigo, " & _
                        "dadpu.DADPU_Codigo, um.UNM_Abreviatura AS ART_UM, (dadpu.DADPU_Cantidad - SUM(ISNULL(dadpu2.DADPU_Cantidad,0))) AS Stock " & _
                        "FROM Documento_Proveedor_Almacen_Detalle dpad " & _
                        "INNER JOIN Documento_Almacen_Detalle dad " & _
                        "ON dpad.CIA_Almacen = dad.CIA AND dpad.SUC_Almacen = dad.SUC " & _
                        "AND dpad.TDOC_Almacen = dad.TDOC_Codigo AND dpad.DCA_Codigo = dad.DCA_Codigo " & _
                        "AND dpad.DAD_Codigo = dad.DAD_Codigo AND dad.DAD_Activo = '1' " & _
                        "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " & _
                        "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC " & _
                        "AND dadp.TDOC_Codigo = dad.TDOC_Codigo AND dadp.DCA_Codigo = dad.DCA_Codigo " & _
                        "AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " & _
                        "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " & _
                        "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo " & _
                        "AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo AND dadpu.DCA_Codigo = dadp.DCA_Codigo " & _
                        "AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " & _
                        "AND dadpu.UBI_Codigo = '0000' " & _
                        "LEFT JOIN Documento_Almacen_Relacion dar " & _
                        "ON dar.CIA_Ingreso = dadpu.CIA AND dar.SUC_Codigo_Ingreso = dadpu.SUC_Codigo " & _
                        "AND dar.TDOC_Codigo_Ingreso = dadpu.TDOC_Codigo AND dar.DCA_Codigo_Ingreso = dadpu.DCA_Codigo " & _
                        "AND dar.DADPU_CodigoIng = dadpu.DADPU_Codigo AND dar.DAR_Activo = '1' " & _
                        "LEFT JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu2 " & _
                        "ON dadpu2.CIA = dar.CIA_Salida AND dadpu2.SUC_Codigo = dar.SUC_Codigo_Salida " & _
                        "AND dadpu2.TDOC_Codigo = dar.TDOC_Codigo_Salida AND dadpu2.DCA_Codigo = dar.DCA_Codigo_Salida " & _
                        "AND dadpu2.DADPU_Codigo = dar.DADPU_CodigoSal " & _
                        "INNER JOIN Articulo a " & _
                        "ON a.ART_Codigo = dad.ART_Codigo AND a.ART_Activo = '1' AND a.ART_Codigo = '" & codBarra & "' " & _
                        "INNER JOIN UnidadMedida um " & _
                        "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " & _
                        "WHERE dpad.CIA_Proveedor = '01' AND dpad.SUC_Proveedor = '01' " & _
                        "AND dpad.TDOC_Proveedor = 'OC' AND dpad.DCP_Codigo = '" & codOrden & "' " & _
                        "GROUP BY a.ART_Codigo, a.ART_Descripcion, dpad.DCA_Codigo, " & _
                        "dadpu.DADPU_Codigo, um.UNM_Abreviatura, dadpu.DADPU_Cantidad " & _
                        "HAVING(dadpu.DADPU_Cantidad - SUM(ISNULL(dadpu2.DADPU_Cantidad, 0))) > 0"
            Else
                query = "SELECT a.ART_Codigo, a.ART_Descripcion, ap.BAR_Codigo, '' AS DCA_Codigo, " & _
                        "(au.UAR_Stock - au.ASIG_UBI) AS Stock, a.ART_UM, '' AS DADPU_Codigo " & _
                        "FROM Articulo a LEFT JOIN ArticuloProveedorCodBarra ap " & _
                        "ON ap.ART_Codigo = a.ART_Codigo " & _
                        "INNER JOIN ArticuloUbicacion au " & _
                        "ON a.ART_Codigo = au.ART_Codigo " & _
                        "WHERE (a.ART_Codigo = '" & codBarra & "' " & _
                        "OR ap.BAR_Codigo = '" & codBarra & "') " & _
                        "AND au.CIA + au.SUC_Codigo + au.ALM_Codigo + " & _
                        "UBI_Codigo = '" & ubiCodigo & "'"
            End If

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function obtenerProveedor(ByVal codProveedor As String) As Proveedor
        Dim prov As Proveedor = Nothing
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT PROV_Codigo,PROV_Descripcion,PROV_DesCorta,PROV_Activo " & _
                                  "FROM Proveedor " & _
                                  "WHERE PROV_Codigo = '" & codProveedor & "' " & _
                                  "AND PROV_Activo = '1'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count <> 0 Then
                Dim codigoProveedor As String = ds.Tables(0).Rows(0)("PROV_Codigo").ToString.Trim
                Dim descripProveedor As String = ds.Tables(0).Rows(0)("PROV_Descripcion").ToString.Trim
                Dim descCorProveedor As String = ds.Tables(0).Rows(0)("PROV_DesCorta").ToString.Trim
                Dim activo As String = ds.Tables(0).Rows(0)("PROV_Activo").ToString.Trim

                prov = New Proveedor(codigoProveedor, descripProveedor, descCorProveedor, activo)
            End If
        Catch ex As Exception
            prov = Nothing
        Finally
            con.Close()
        End Try

        Return prov
    End Function

    Function mostrarUnidadesMedida() As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT UNM_Codigo,UNM_Abreviatura,UNM_Descripcion " & _
                                  "FROM UnidadMedida " & _
                                  "WHERE UNM_Activo = '1'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function listarArticulosXProveedor(ByVal codProveedor As String) As DataView
        Dim dv As DataView = Nothing
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT a.ART_Codigo,a.ART_Descripcion,a.ART_UM " & _
                                  "FROM ArticuloProveedor ap " & _
                                  "INNER JOIN Articulo a ON a.ART_Codigo = ap.ART_Codigo " & _
                                  "WHERE ap.PROV_Codigo = '" & codProveedor & "' " & _
                                  "AND a.ART_Activo = '1' AND len(a.ART_Codigo) > 6 " & _
                                  "AND ap.ARP_Activo = '1'"

            Dim ds As New DataSet
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds, "Listado")
            dv = ds.Tables(0).DefaultView
        Catch ex As Exception
            dv = Nothing
        Finally
            con.Close()
        End Try

        Return dv
    End Function

    Function registrarBarra(ByVal barra As ArticuloProveedorCodBarra) As String
        Dim mensaje As String = ""
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "INSERT INTO ArticuloProveedorCodBarra(CIA,ART_Codigo,PROV_Codigo, " & _
                                  "BAR_Codigo,UNM_Codigo,APC_Cantidad) " & _
                                  "VALUES(@cia,@articulo,@proveedor,@barra,@unidad,@cantidad)"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@cia", barra.CIA)
                .Parameters.AddWithValue("@articulo", barra.ART_Codigo)
                .Parameters.AddWithValue("@proveedor", barra.PROV_Codigo)
                .Parameters.AddWithValue("@barra", barra.BAR_Codigo)
                .Parameters.AddWithValue("@unidad", barra.UND_Medida)
                .Parameters.AddWithValue("@cantidad", barra.CANTIDAD)
                .ExecuteNonQuery()
            End With

            mensaje = "Registro Exitoso."
        Catch ex As Exception
            mensaje = "-1"
        Finally
            con.Close()
        End Try

        Return mensaje

        Return mensaje
    End Function

    Function verificarExistenciaCODBARRA(ByVal cia As String, ByVal codBarra As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT a.ART_Descripcion, um.UNM_Abreviatura, apc.APC_Cantidad, p.PROV_DesCorta " & _
                                  "FROM ArticuloProveedorCodBarra apc " & _
                                  "INNER JOIN Articulo a ON a.ART_Codigo = apc.ART_Codigo " & _
                                  " AND a.ART_Activo = '1' " & _
                                  "INNER JOIN UnidadMedida um ON um.UNM_Codigo = apc.UNM_Codigo " & _
                                  " AND UNM_Activo = '1' " & _
                                  "INNER JOIN Proveedor p ON p.PROV_Codigo = apc.PROV_Codigo " & _
                                  " AND p.PROV_Activo = '1' " & _
                                  "WHERE apc.BAR_Codigo = '" & codBarra & "' AND apc.CIA = '" & cia & "'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function listarProveedores() As DataView
        Dim dv As DataView = Nothing
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT PROV_Codigo,PROV_Descripcion,PROV_DesCorta " & _
                                  "FROM Proveedor WHERE PROV_Activo = '1'"

            Dim ds As New DataSet
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds, "Proveedores")
            dv = ds.Tables(0).DefaultView
        Catch ex As Exception
            dv = Nothing
        Finally
            con.Close()
        End Try

        Return dv
    End Function

End Class
